package book.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import book.dao.IBookDao;
import book.db.DbConnection;
import book.po.Book;


/**
 * 图书dao实现类
 *
 */
public class BookDaoImpl implements IBookDao {
	/**
	 * 新增图书
	 *
	 */
	@Override
	public void save(Book book) {
		Connection conn = DbConnection.getConnection();
		int isbn = book.getIsbn();
		String name = book.getName();
		double price = book.getPrice();
		String sql = "insert into book values(?,?,?)";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setInt(1, isbn);
			ps.setString(2, name);
			ps.setDouble(3, price);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(ps, null);
		}
		
	}
 
	/**
	 *修改图书
	 */
	@Override
	public void update(Book book) {
		Connection conn = DbConnection.getConnection();
		String name = book.getName();
		double price = book.getPrice();
		int isbn = book.getIsbn();
		String sql = "update book set name=?,price=? where isbn=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setDouble(2, price);
			ps.setInt(3, isbn);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, null);
		}
		
	}
 
	/**
	 *删除指定图书
	 *id为主键
	 */
	@Override
	public void delete(int id) { 
		Connection conn = DbConnection.getConnection();
		String sql = "delete from book where isbn=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, null);
		}
	}
	
	/**
	 *根据图书编号查询图书
	 *id为主键
	 */
	@Override
	public Book get(int id) { //id为主键
		Connection conn = DbConnection.getConnection();
		List<Book> bookList = new ArrayList<Book>();
		String sql = "select isbn,name,price from book where isbn=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()){
				int isbn = rs.getInt(1);
				String name = rs.getString(2);
				double price = rs.getDouble(3);
				Book book = new Book(isbn,name,price);
				bookList.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookList.get(0);	//返回第一本书，集合 索引为0
	}
 
	/**
	 *查询所有图书
	 */
	@Override
	public List<Book> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Book> bookList = new ArrayList<Book>();
		String sql = "select isbn,name,price from book";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				int isbn = rs.getInt(1);
				String name = rs.getString(2);
				double price = rs.getDouble(3);
				Book book = new Book(isbn,name,price);
				bookList.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookList;
	}

	@Override
	public void update(int id, Book book) { //id为list的index。为保持兼容内存及文件存储，service依旧调用该接口。但数据库dao的update方法不再需要index。
		this.update(book);
	}
}